signon ;		
rsubmit;
libname bankrup ''; 
libname input   '' ;
libname crime	'';
libname par18 '';
libname parkid '' ;
options compress=YES;

* BROADER SSI SAMPLE USING 2002-2012 COHORTS AND 1990-2002 COHORTS ;

* Prep 2002-2012 SSR sample ;
data childrd_samp ;
	set input.kids_ssr_full_nwithsumm
		(keep= hun dob sex race dibdig dibmdr toa pdscc n_estpd_num
		 rename=(dob=dob_weird))
	;
		dob_char = put(dob_weird,8.) ;
		dobyy = substr(dob_char,1,4) ;
		dobmm = substr(dob_char,5,2) ;
		dobdd = substr(dob_char,7,2) ;
		dob = mdy(dobmm,dobdd,dobyy) ;

		if (toa = "DM" | toa = "BM" | toa = "DF" | toa = "BF" | toa = "DC" | toa = "BC") 
			then n_fam_singno = 1 ; else n_fam_singno = 0 ;
		if (toa = "DB" | toa = "BB") 
			then n_fam_twopar = 1; else n_fam_twopar = 0 ;

		if dibmdr = "3" 
			then n_dibmdr_mip = 1 ; else n_dibmdr_mip = 0 ;
		if (dibmdr = "5" | dibmdr = "7" | dibmdr = "9" )
			then n_dibmdr_mine = 1 ; else n_dibmdr_mine = 0 ;
		if (dibmdr ^= "" & dibmdr ^= "3" & dibmdr ^= "5" & dibmdr ^= "7" & dibmdr ^= "9" )
			then n_dibmdr_mie = 1 ; else n_dibmdr_mie = 0 ;

		if race ^= "W" & race ^= "" & race ^= "U" then n_race_nonw = 1 ; 
			else if (race = "U" | race = "") then n_race_nonw = . ;
			else n_race_nonw = 0 ;
		if race = "B" then n_race_black = 1 ; 
			else if (race = "U" | race = "") then n_race_black = . ;
			else n_race_black = 0 ;
		if race = "W" then n_race_white = 1 ; 
			else if (race = "U" | race = "") then n_race_white = . ;
			else n_race_white = 0 ;

		drop dob_weird dob_char dobmm dobdd dobyy ;

	
run ;

proc sort data=childrd_samp out=childrd_samp ;
	by hun n_estpd_num;
run;

proc sort data=childrd_samp out=childrd_samp_same nodupkey ;
	by hun;
run;

proc means data=childrd_samp noprint;
	by hun ;
	var n_dibmdr_: n_fam_: n_race_: ;
	output out=childrd_samp_max 
		max(n_dibmdr_: n_fam_: n_race_: )= ;
run;

data childrd_samp ;

	merge 
		childrd_samp_same
			(keep = hun dob sex dibdig pdscc)

		childrd_samp_max
			(rename=(n_dibmdr_mie=max_dibmdr_mie 
					n_dibmdr_mine=max_dibmdr_mine 
					n_dibmdr_mip=max_dibmdr_mip
					n_fam_singno=max_fam_singno 
					n_fam_twopar=max_fam_twopar
					n_race_white=max_race_white
					n_race_black=max_race_black
					n_race_nonw=max_race_nonw
					))

		parkid.par_earn_hunmerge_wide
			(keep=hun n_earnmom_1985-n_earnmom_2011 
					  n_earndad_1985-n_earndad_2011
					  n_hhearns_1985-n_hhearns_2011
			in=from_parearnx)
		;

		by hun;
		from_parearn = from_parearnx ;
	run;

* Prep 1990-2002 SSR sample ;
data age18rd_samp ;
	set input.prwora_age18_prep2
		(keep= hun birth_jd sex race dib_dig dib_mdr toa pdscc rcd_est_jd
		 rename=(birth_jd=dob dib_dig=dibdig dib_mdr=dibmdr))
	;

		if (toa = "DM" | toa = "BM" | toa = "DF" | toa = "BF" | toa = "DC" | toa = "BC") 
			then n_fam_singno = 1 ; else n_fam_singno = 0 ;
		if (toa = "DB" | toa = "BB") 
			then n_fam_twopar = 1; else n_fam_twopar = 0 ;

		if dibmdr = "3" 
			then n_dibmdr_mip = 1 ; else n_dibmdr_mip = 0 ;
		if (dibmdr = "5" | dibmdr = "7" | dibmdr = "9" )
			then n_dibmdr_mine = 1 ; else n_dibmdr_mine = 0 ;
		if (dibmdr ^= "" & dibmdr ^= "3" & dibmdr ^= "5" & dibmdr ^= "7" & dibmdr ^= "9" )
			then n_dibmdr_mie = 1 ; else n_dibmdr_mie = 0 ;

		if race ^= "W" & race ^= "" & race ^= "U" then n_race_nonw = 1 ; 
			else if (race = "U" | race = "") then n_race_nonw = . ;
			else n_race_nonw = 0 ;
		if race = "B" then n_race_black = 1 ; 
			else if (race = "U" | race = "") then n_race_black = . ;
			else n_race_black = 0 ;
		if race = "W" then n_race_white = 1 ; 
			else if (race = "U" | race = "") then n_race_white = . ;
			else n_race_white = 0 ;
run ;

proc sort data=age18rd_samp out=age18rd_samp ;
	by hun rcd_est_jd ;
run;

proc sort data=age18rd_samp out=age18rd_samp_same nodupkey ;
	by hun;
run;

proc means data=age18rd_samp noprint;
	by hun ;
	var n_dibmdr_: n_fam_: n_race_: ;
	output out=age18rd_samp_max 
		max(n_dibmdr_: n_fam_: n_race_: )= ;
run;

data age18rd_samp ;

	merge 
		age18rd_samp_same
			(keep = hun dob sex race dibdig pdscc)

		age18rd_samp_max
			(rename=(n_dibmdr_mie=max_dibmdr_mie 
					n_dibmdr_mine=max_dibmdr_mine 
					n_dibmdr_mip=max_dibmdr_mip
					n_fam_singno=max_fam_singno 
					n_fam_twopar=max_fam_twopar
					n_race_white=max_race_white
					n_race_black=max_race_black
					n_race_nonw=max_race_nonw
					))

		par18.prwora_par_earn_hunmerge_wide 
			(in=from_parearnx  
			keep=hun n_hhearns_1985-n_hhearns_2012)
		;

		by hun;
		from_parearn = from_parearnx ; 
	run;

* Append the samples ;
data crime.allssr_crimekid ;
	set 
			age18rd_samp (drop = _type_ _freq_)
			childrd_samp (drop = _type_ _freq_)
			
			;
run ;

proc means data = crime.allssr_crimekid ;
	var from_parearn ;
run ;

proc sort data=crime.allssr_crimekid out=crime.allssr_crimekid nodupkey ;
	by hun;
run;

data crime.allssr_crimekid;

	merge 
		crime.allssr_crimekid
			(in=from_mainx)

		bankrup.bankrup_ssrformerge 
			(in=from_namex
			rename=(ssn=hun lastn=fssr_lastn firstn=fssr_firstn middlen=fssr_middlen))
		;

		by hun;
		if from_mainx=1;
		from_name=from_namex;
	run;

* See how many merge to name file ;
proc means data=crime.allssr_crimekid;
	var from_name;
run;

* Exclude anyone with missing name or DOB ;
data crime.allssr_crimekid ;
	set crime.allssr_crimekid 
	(where=(dob^=. & fssr_firstn ^= "" & fssr_lastn ^= "")
	 drop=from_name)
	;
run ;

*--------------------*;
* Export Stata files *;
*--------------------*;
%let output = 	 ;
%macro stataexport(dsn);
    proc export data=crime.&dsn
        file="&output\&dsn..dta"
        dbms=DTA REPLACE;
    run;
%mend stataexport;

%stataexport(allssr_crimekid);

endrsubmit;
signoff ;
